<?php
class Income_expenses extends Admin_Controller
{	
	
	public function __construct()
	{
		parent::__construct();
		$this->check_fisrt_start();
		$this->load->model('voucher/fund_model', 'fund');
		$this->load->model('registration/register_model','register');
		$this->load->model('registration/register_detail_model','register_detail');
		$this->load->model('registration/register_title_model','register_title');
		$this->load->model('registration/register_column_model','register_column');
	}
	
	public function view($fund_id, $year, $month, $agency_id = NULL)
	{
		$agency_id = (empty($agency_id)) ? login_data('agency_id') : $agency_id;
		// ค้นหา
		if($_GET)
		{
			$agency_id = (empty($_GET['agency_id'])) ? NULL : '/'.$_GET['agency_id'];
			redirect('income_expenses/view/'.$fund_id.'/'.$_GET['year'].'/'.$_GET['month'].$agency_id);
			exit;
		}
		$data['agency_id'] = $agency_id;
		$data['year'] = $year;
		$data['month'] = $month;
		$data['fund'] = $this->fund->get_row($fund_id);
		$year = Fund::convert_to_real_year($year, $month);
		$data['side_label'] = $this->register->side;
		$data['sub_label'] = $this->register->sub;		
		$data['haystack'] = $this->register->col_not_price;
		$data['result'] = $this->register->income_expenses($fund_id, $agency_id, $year, $month);
		$data['first_budget'] = $this->get_budget_first_month($fund_id, $agency_id, $year, $month);
		
		$this->template->append_metadata(js_print());
		$this->template->build('view', $data);
	}
	
	public function view_all($fund_id, $year, $month)
	{
		$agency_id = login_data('agency_id');
		// ค้นหา
		if($_GET)
		{
			$agency_id = (empty($_GET['agency_id'])) ? NULL : '/'.$_GET['agency_id'];
			redirect('income_expenses/view_all/'.$fund_id.'/'.$_GET['year'].'/'.$_GET['month']);
			exit;
		}
		$data['agency_id'] = $agency_id;
		$data['year'] = $year;
		$data['month'] = $month;
		$data['fund'] = $this->fund->get_row($fund_id);
		$year = Fund::convert_to_real_year($year, $month);
		$data['side_label'] = $this->register->side;
		$data['sub_label'] = $this->register->sub;		
		$data['haystack'] = $this->register->col_not_price;
		$data['result'] = $this->register->income_expenses_all($fund_id, $year, $month);
		$data['first_budget'] = $this->get_budget_first_month_all($fund_id, $year, $month);
		
		$this->template->append_metadata(js_print());
		$this->template->build('view_all', $data);
	}
	
	public function get_budget_first_month($fund_id, $agency_id, $year, $month)
	{	
		$first_month = Fund::fix_budget($year, $month, TRUE);
		$sql = "SELECT PRICE
		FROM REGISTERS
		JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
		WHERE REGISTERS.FUND_ID = ?
		AND REGISTERS.AGENCY_ID = ?
		AND REGISTER_DETAILS.COL = 58
		AND REGISTERS.DATE_RECORD BETWEEN ? AND ? 
		ORDER BY REGISTER_DETAILS.ID ASC LIMIT 1";
		return $this->db->getone($sql, array($fund_id, $agency_id, $first_month[0], $first_month[1]));
	}
	
	public function get_budget_first_month_all($fund_id, $year, $month)
	{
		$first_month = Fund::fix_budget($year, $month, TRUE);
		$sql = "SELECT SUM(PRICE) AS PRICE
		FROM REGISTERS
		JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
		WHERE REGISTERS.FUND_ID = ?
		AND REGISTER_DETAILS.COL = 58
		AND REGISTERS.DATE_RECORD BETWEEN ? AND ? 
		GROUP BY TITLE_ID
		";
		return $this->db->getone($sql, array($fund_id, $first_month[0], $first_month[1]));
	}
	
	/*
	 * การบันทึกบัญชีใบสำคัญรับเงินส่วนของพมจ.
	 */
	public function all($fund_id, $year, $month)
	{
		// ค้นหา
		if($_GET)
		{
			redirect('income_expenses/all/'.$fund_id.'/'.$_GET['year'].'/'.$_GET['month']);
			exit;
		}
		$data['year'] = $year;
		$data['month'] = $month;
		$data['fund'] = $this->fund->get_row($fund_id);
		
		// ตารางการส่งข้อมูลทุกจังหวัด
		$sql = 'SELECT A.NAME, B.AGENCY_ID, A.ID 
		FROM AGENCIES AS A
		LEFT JOIN (
			SELECT BALANCES.AGENCY_ID
			FROM BALANCES
			WHERE BALANCES.FUND_ID = ?
			AND YEAR_RECORD = ?
			AND MONTH_RECORD = ?
			) AS B ON B.AGENCY_ID = A.ID
		WHERE A.ID not in (1,2,79) 
		ORDER BY A.ID';
		$data['provinces'] = $this->db->getarray($sql, array($fund_id, ($year - 543), $month));
		dbConvert($data['provinces']);
		
		$data['province_complete'] = $this->db->getone('SELECT COUNT(BALANCES.AGENCY_ID)
		FROM BALANCES
		WHERE BALANCES.FUND_ID = ?
		AND YEAR_RECORD = ?
		AND MONTH_RECORD = ?',
		array($fund_id, ($year - 543), $month)); 
		
		// การบันทึกบัญชีใบสำคัญรับเงินส่วนของพมจ.
		$sql = 'SELECT REGISTER_TITLES.ID, REGISTER_TITLES.COL_TITLE, REGISTER_TITLES.SIDE, REGISTER_TITLES.SUB, DETAILS.COL, DETAILS.TOTAL_PRICE, REGISTER_TITLES.ACCOUNT_ID'.$fund_id.' AS ACCOUNT_ID 
		FROM REGISTER_TITLES
		JOIN (
			SELECT REGISTERS.TITLE_ID, REGISTER_DETAILS.COL, SUM(REGISTER_DETAILS.PRICE) AS TOTAL_PRICE
			FROM REGISTERS
			JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
			WHERE REGISTERS.FUND_ID = ?
			AND YEAR(REGISTERS.DATE_RECORD) = ?
			AND MONTH(REGISTERS.DATE_RECORD) = ?	
			AND REGISTER_DETAILS.IS_PRICE = 1
			GROUP BY REGISTER_DETAILS.COL, REGISTERS.TITLE_ID
			) AS DETAILS ON DETAILS.TITLE_ID = REGISTER_TITLES.ID
		WHERE REGISTER_TITLES.SUB IN (2, 3, 5)  
		ORDER BY REGISTER_TITLES.ID ASC';
		$data['vouchers'] = $this->db->getarray($sql, array($fund_id, ($year-543), $month));
		dbConvert($data['vouchers']);
		
		$data['voucher_total'] = $this->db->getassoc('SELECT REGISTERS.SIDE, SUM(REGISTER_DETAILS.PRICE) AS TOTAL_PRICE
		FROM REGISTERS
		JOIN REGISTER_DETAILS ON REGISTER_DETAILS.REGISTER_ID = REGISTERS.ID
		WHERE REGISTERS.FUND_ID = ?
		AND YEAR(REGISTERS.DATE_RECORD) = ?
		AND MONTH(REGISTERS.DATE_RECORD) = ?	
		AND REGISTER_DETAILS.IS_PRICE = 1
		AND SUB IN (2,3,5)
		GROUP BY REGISTERS.SIDE', array($fund_id, ($year-543), $month));
		dbConvert($data['voucher_total']);
		
		// เรียกข้อมูลใบสำคัญมาแก้ไข
		$data['rs'] = NULL;
		$rs = $this->db->getrow('SELECT * 
		FROM VOUCHERS
		WHERE VOUCHERS.FUND_ID = ? 
		AND YEAR(VOUCHERS.VOUCHER_DATE) = ?
		AND MONTH(VOUCHERS.VOUCHER_DATE) = ?',
		array($fund_id, ($year - 543), $month));
		if(!empty($rs))
		{
			dbConvert($rs);
			$data['rs'] = $rs;
		}
		
		$this->template->append_metadata(js_datepicker());
		$this->template->build('all', $data);
	}
	
	public function save($fund_id, $year, $month)
	{
		if($_POST)
		{
			$this->load->model('voucher/voucher_model', 'voucher');
			$this->load->model('voucher/voucher_detail_model', 'voucher_detail');
			
			if(empty($_POST['id']))
			{
				add_log('เพิ่มการบันทึกบัญชีใบสำคัญรับเงินส่วนของพมจ. กองทุน  "'.$this->fund->get_one('name',$_POST['fund_id']).'" วันเดือนปีที่ลงบัญชี '.$_POST['voucher_record']);
			}
			else
			{
				add_log('แก้ไขการบันทึกบัญชีใบสำคัญรับเงินส่วนของพมจ. กองทุน  "'.$this->fund->get_one('name',$_POST['fund_id']).'" วันเดือนปีที่ลงบัญชี '.$_POST['voucher_record']);
			}
			
			// ลบใบสำคัญเพื่อแก้ไขใหม่
			$ids = $this->db->getarray('SELECT ID FROM VOUCHERS WHERE FUND_ID = ? AND YEAR(VOUCHER_DATE) = ? AND MONTH(VOUCHER_DATE) = ?', array($fund_id, ($year - 543), $month));		
			foreach($ids as $id)
			{
				$this->db->execute('DELETE FROM VOUCHERS WHERE ID = ?', array($id));
				$this->db->execute('DELETE FROM VOUCHER_DETAILS WHERE VOUCHER_ID = ?', array($id));
			}				
			
			foreach(range(1, 2) as $side)
			{
				if(!empty($_POST['type'][$side]))
				{
					$voucher = array(
						'number' => $_POST['number'],
						'voucher_type_id' => 3,
						'description' => $_POST['description'],
						'user_id' => login_data('id'),
						'fund_id' => $fund_id,
						'agency_id' => 79,
						'voucher_date' => date_to_mysql($_POST['voucher_date'], TRUE)
					);
					$voucher_id = $this->voucher->save($voucher);
					
					foreach($_POST['type'][$side] as $account_id => $type)
					{
						$voucher_detail = array(
							'voucher_id' => $voucher_id,
							'type' => $type,
							'account_id' => $account_id,
							'price' => $_POST['price'][$side][$account_id]
						);	
						$this->voucher_detail->save($voucher_detail);
					}
				}
			}
			set_notify('success', lang('save_data_complete'));
		}
		redirect('income_expenses/all/'.$fund_id.'/'.$year.'/'.$month);
	}
	
	public function code($fund_id)
	{
		if($_POST)
		{
			foreach($_POST['account_id'] as $id => $account_id)
			{
				$dataset = array(
					'id' => $id,
					'account_id'.$fund_id => trim($account_id)
				);
				$this->register_title->save($dataset);
			}
		}
		$data['fund'] = $this->fund->get_row($fund_id);
		$data['items'] = $this->db->getarray('SELECT * FROM REGISTER_TITLES WHERE SUB IN (2, 3, 5) ORDER BY ID ASC');
		dbConvert($data['items']);
		$this->template->build('code', $data);
	}
}
?>